
/* This program compares the annual begin-of-year ratings between different CRAs of(SP and Moody) on */
/* jointly rated issues.     */
								 

libname libone 'C:\Users\m40lxz1\OneDrive - Northern Illinois University\quality\sas\sasdata\';


data ejr;
 set libone.annual_ejr_rating;
NRSRO='EJR  '; run;
 
data SP;
 set libone.annual_sp_rating;
 NRSRO='SP  ';
 
data mdy;
 set libone.annual_mdy_rating;
 NRSRO='MDY  ';

data fitch;
 set libone.annual_fitch_rating;
NRSRO='Fitch'; run;



/*************************************************************************************************/
/* The followinh two step compares and test the differences in the beg-of-year average ratings.    */
/* The results go to Table 3                                                                     */
/*************************************************************************************************/

data pair;
 set ejr sp;
 where cusip ne '';run;

proc sort data=pair; by NRSRO;

ods graphics off;

proc ttest data=pair;
 class NRSRO;
 var beg2014 beg2015 beg2016 beg2017 beg2018 beg2019 beg2020 beg2021;run;

 /*********************************************************************************************************/

data ejr;
 set ejr;
 rename beg2013=EJR13 beg2014=EJR14 beg2015=EJR15 beg2016=EJR16 beg2017=EJR17 beg2018=EJR18
 beg2019=EJR19 beg2020=EJR20 beg2021=EJR21;

data MDY;
 set MDY;
 rename beg2013=MDY13 beg2014=MDY14 beg2015=MDY15 beg2016=MDY16 beg2017=MDY17 beg2018=MDY18
 beg2019=MDY19 beg2020=MDY20 beg2021=MDY21;

 data SP;
 set SP;
 rename beg2013=SP13 beg2014=SP14 beg2015=SP15 beg2016=SP16 beg2017=SP17 beg2018=SP18
 beg2019=SP19 beg2020=SP20 beg2021=SP21;

 data Fitch;
 set Fitch;
 rename beg2013=Fitch13 beg2014=Fitch14 beg2015=Fitch15 beg2016=Fitch16 beg2017=Fitch17 beg2018=Fitch18
 beg2019=Fitch19 beg2020=Fitch20 beg2021=Fitch21;




/********************************************************************************************/

proc sql;
 create table sp_ejr as 
 select *
 from ejr as A
 inner join sp as B
 on A.ID=B.ID;

run;

data sp_EJR;
 set sp_EJR;
 SPMEJR13=sp13-ejr13;
 SPMEJR14=sp14-ejr14;
 SPMEJR15=sp15-ejr15;
 SPMEJR16=sp16-ejr16;
 SPMEJR17=sp17-ejr17;
 SPMEJR18=sp18-ejr18;
 SPMEJR19=sp19-ejr19;
 SPMEJR20=sp20-ejr20;
 SPMEJR21=sp21-EJR21;

proc means data=sp_ejr n mean median min max;
 var spmejr13 spmejr14 spmejr15 spmejr16 spmejr17 spmejr18 spmejr19 spmejr20 spmejr21;run;

data sp_EJR;
 set sp_EJR;
 if spmejr13=. then do; sp13=.; ejr13=.; end;
 if spmejr14=. then do; sp14=.; ejr14=.; end;
 if spmejr15=. then do; sp15=.; ejr15=.; end;
 if spmejr16=. then do; sp16=.; ejr16=.; end;
 if spmejr17=. then do; sp17=.; ejr17=.; end;
 if spmejr18=. then do; sp18=.; ejr18=.; end;
 if spmejr19=. then do; sp19=.; ejr19=.; end;
 if spmejr20=. then do; sp20=.; ejr20=.; end;
 if spmejr21=. then do; sp21=.; ejr21=.; end;



* The following sql gets the data on the joint-rating status of Moody's and S&P;
* IF mdy_vs_sp=1 then Moody's has a better rating than S&P;
* IF mdy_vs_sp=-1 then moody's has a worse rating than S&P;
* IF mdy_vs_sp=0 then moody's and S&P have the same rating;
* IF mdy_vs_sp=. then not joint rated by Moody's and S&P;
* the sas data file mdy_sp_joint is created by the sas proram SP_MDY_joint; 

proc sql;
 create table SP_EJR_expand as 											
 select *
 from SP_EJR as A
 left join libone.mdy_sp_joint as B
 on A.ID=B.CUSIP;

run;


proc means data=sp_ejr_expand noprint;
 var sp14 sp15 sp16 sp17 sp18 sp19 sp20 sp21 ejr14 ejr15 ejr16 ejr17 ejr18 ejr19 ejr20 ejr21;
 output out=joint_mean mean =first2014 first2015 first2016 first2017 first2018 first2019 first2020
first2021 second2014 second2015 second2016 second2017 second2018 second2019 second2020 second2021
 n = obs2014 obs2015 obs2016 obs2017 obs2018 obs2019 obs2020 obs2021; 
where mdy_vs_sp =0 or mdy_vs_sp=.; 							* delete moody's and S&P split ratings;
* where mdy_vs_sp=-1;
* where mdy_vs_sp=.;
run; 


data joint_mean;
 set joint_mean;
 drop _TYPE_ _FREQ_; 

proc transpose data=joint_mean out=joint_mean_list;

data first;
 set joint_mean_list;
 if index(_Name_, 'first') ne 0;run;

data first;
 set first;
 year=substr(_Name_, 6,4)*1;
 rename col1=first;
 drop _Name_;run;

data second;
  set joint_mean_list;
 if index(_Name_, 'second') ne 0;run;

 data second;
 set second;
 year=substr(_Name_, 7,4)*1;
 rename col1=second;
 drop _Name_;run;

 data third;
  set joint_mean_list;
 if index(_Name_, 'obs') ne 0;run;

 data third;
 set third;
 year=substr(_Name_, 4,4)*1;
 rename col1=Number;
 drop _Name_;run;

data combine;
 merge first second third;
 by year;run;

data combine;
 set combine;
 year=year-1;																		* change beg-year to end-year;

  ods graphics on;
ODS html SGE=ON;

 proc sgplot data=combine;
 series X=year Y=first/ legendlabel='S&P' markers markerattrs=(color= blue symbol =circlefilled size=10) 
lineattrs=(pattern=1 color=blue thickness=3);
 series X=year Y=second / legendlabel='EJR' markers markerattrs=(color= purple symbol =squarefilled size=10) 
lineattrs=(pattern=4 color=purple thickness=3);
yaxis integer min=7 max=10.5 valueattrs=(family='times new roman' size=12);
 XAXIS TYPE = DISCRETE valueattrs=(family='times new roman' size=12); run;

proc delete data=first second third combine;run;


/*******************************************************************************************/

 proc sql;
 create table sp_mdy as 
 select *
 from mdy as A
 inner join sp as B
 on A.ID=B.ID;

run;

data sp_mdy;
 set sp_mdy;
 SPMMDY13=sp13-MDY13;
 SPMMDY14=sp14-MDY14;
 SPMMDY15=sp15-MDY15;
 SPMMDY16=sp16-MDY16;
 SPMMDY17=sp17-MDY17;
 SPMMDY18=sp18-MDY18;
 SPMMDY19=sp19-MDY19;
 SPMMDY20=sp20-MDY20;
 SPMMDY21=sp21-MDY21;

proc means data=sp_MDY n mean median min max;
 var spmMDY13 spmMDY14 spmMDY15 spmMDY16 spmMDY17 spmMDY18 spmMDY19 spmMDY20 spmMDY21;run;


data sp_mdy;
 set sp_mdy;
 if spmMDY13=. then do; sp13=.; MDY13=.; end;
 if spmMDY14=. then do; sp14=.; MDY14=.; end;
 if spmMDY15=. then do; sp15=.; MDY15=.; end;
 if spmMDY16=. then do; sp16=.; MDY16=.; end;
 if spmMDY17=. then do; sp17=.; MDY17=.; end;
 if spmMDY18=. then do; sp18=.; MDY18=.; end;
 if spmMDY19=. then do; sp19=.; MDY19=.; end;
 if spmMDY20=. then do; sp20=.; MDY20=.; end;
 if spmMDY21=. then do; sp21=.; MDY21=.; end;

proc means data=sp_mdy noprint;
 var sp14 sp15 sp16 sp17 sp18 sp19 sp20 sp21 mdy14 mdy15 mdy16 mdy17 mdy18 mdy19 mdy20 mdy21;
 output out=joint_mean mean =first2014 first2015 first2016 first2017 first2018 first2019 first2020
first2021 second2014 second2015 second2016 second2017 second2018 second2019 second2020 second2021
 n = obs2014 obs2015 obs2016 obs2017 obs2018 obs2019 obs2020 obs2021; 
run; 

data joint_mean;
 set joint_mean;
 drop _TYPE_ _FREQ_; 

proc transpose data=joint_mean out=joint_mean_list;

data first;
 set joint_mean_list;
 if index(_Name_, 'first') ne 0;run;

data first;
 set first;
 year=substr(_Name_, 6,4)*1;
 rename col1=first;
 drop _Name_;run;

data second;
  set joint_mean_list;
 if index(_Name_, 'second') ne 0;run;

 data second;
 set second;
 year=substr(_Name_, 7,4)*1;
 rename col1=second;
 drop _Name_;run;

 data third;
  set joint_mean_list;
 if index(_Name_, 'obs') ne 0;run;

 data third;
 set third;
 year=substr(_Name_, 4,4)*1;
 rename col1=Number;
 drop _Name_;run;

data combine;
 merge first second third;
 by year;run;

 data combine;
 set combine;
 year=year-1;

 proc sgplot data=combine;
 series X=year Y=first/ legendlabel='S&P' markers markerattrs=(color= blue symbol =circlefilled size=10) 
lineattrs=(pattern=1 color=blue thickness=3);
 series X=year Y=second / legendlabel="Moody's" markers markerattrs=(color= orange symbol =diamondfilled size=10) 
lineattrs=(pattern=5 color=orange thickness=3);
yaxis integer min=7 max=10.5 valueattrs=(family='times new roman' size=12);
 XAXIS TYPE = DISCRETE valueattrs=(family='times new roman' size=12); run;

proc delete data=first second third combine;run;
/********************************************************************************************/

proc sql;
 create table MDY_ejr as 
 select *
 from ejr as A
 inner join MDY as B
 on A.ID=B.ID;

run;

data MDY_EJR;
 set MDY_EJR;
 MDYMEJR13=MDY13-ejr13;
 MDYMEJR14=MDY14-ejr14;
 MDYMEJR15=MDY15-ejr15;
 MDYMEJR16=MDY16-ejr16;
 MDYMEJR17=MDY17-ejr17;
 MDYMEJR18=MDY18-ejr18;
 MDYMEJR19=MDY19-ejr19;
 MDYMEJR20=MDY20-ejr20;
 MDYMEJR21=MDY21-EJR21;

proc means data=MDY_ejr n mean median min max;
 var MDYmejr13 MDYmejr14 MDYmejr15 MDYmejr16 MDYmejr17 MDYmejr18 MDYmejr19 MDYmejr20 MDYmejr21;run;

data mdy_ejr;
 set MDY_EJR;
 if MDYmEJR13=. then do; MDY13=.; EJR13=.; end;
 if MDYmEJR14=. then do; MDY14=.; EJR14=.; end;
 if MDYmEJR15=. then do; MDY15=.; EJR15=.; end;
 if MDYmEJR16=. then do; MDY16=.; EJR16=.; end;
 if MDYmEJR17=. then do; MDY17=.; EJR17=.; end;
 if MDYmEJR18=. then do; MDY18=.; EJR18=.; end;
 if MDYmEJR19=. then do; MDY19=.; EJR19=.; end;
 if MDYmEJR20=. then do; MDY20=.; EJR20=.; end;
 if MDYmEJR21=. then do; MDY21=.; EJR21=.; end;


* The following sql gets the data on the joint-rating status of Moody's and S&P;
* IF mdy_vs_sp=1 then Moody's has a better rating than S&P;
* IF mdy_vs_sp=-1 then moody's has a worse rating than S&P;
* IF mdy_vs_sp=0 then moody's and S&P have the same rating;
* IF mdy_vs_sp=. then not joint rated by Moody's and S&P;
* the sas data file mdy_sp_joint is created by the sas proram SP_MDY_joint; 

proc sql;
 create table MDY_EJR_expand as 											
 select *
 from MDY_EJR as A
 left join libone.mdy_sp_joint as B
 on A.ID=B.CUSIP;

run;



proc means data=MDY_EJR_expand noprint;
 var MDY14 MDY15 MDY16 MDY17 MDY18 MDY19 MDY20 MDY21 EJR14 EJR15 EJR16 EJR17 EJR18 EJR19 EJR20 EJR21;
 output out=joint_mean mean =first2014 first2015 first2016 first2017 first2018 first2019 first2020
first2021 second2014 second2015 second2016 second2017 second2018 second2019 second2020 second2021
 n = obs2014 obs2015 obs2016 obs2017 obs2018 obs2019 obs2020 obs2021; 
where mdy_vs_sp=0 or mdy_vs_sp=.; 									* delete Moody's and S&P split ratings; 
*where mdy_vs_sp = 1;
run; 

data joint_mean;
 set joint_mean;
 drop _TYPE_ _FREQ_; 

proc transpose data=joint_mean out=joint_mean_list;

data first;
 set joint_mean_list;
 if index(_Name_, 'first') ne 0;run;

data first;
 set first;
 year=substr(_Name_, 6,4)*1;
 rename col1=first;
 drop _Name_;run;

data second;
  set joint_mean_list;
 if index(_Name_, 'second') ne 0;run;

 data second;
 set second;
 year=substr(_Name_, 7,4)*1;
 rename col1=second;
 drop _Name_;run;

 data third;
  set joint_mean_list;
 if index(_Name_, 'obs') ne 0;run;

 data third;
 set third;
 year=substr(_Name_, 4,4)*1;
 rename col1=Number;
 drop _Name_;run;

data combine;
 merge first second third;
 by year;run;

 
data combine;
 set combine;
 year=year-1;	

 proc sgplot data=combine;
 series X=year Y=first/ legendlabel='MDY' markers markerattrs=(color= orange symbol =diamondfilled size=10) 
lineattrs=(pattern=5 color=orange thickness=3);
 series X=year Y=second / legendlabel='EJR' markers markerattrs=(color= purple symbol =squarefilled size=10) 
lineattrs=(pattern=4 color=purple thickness=3);
yaxis integer min=7 max=10.5 valueattrs=(family='times new roman' size=12);
 XAXIS TYPE = DISCRETE valueattrs=(family='times new roman' size=12); run;

proc delete data=first second third combine;run;

/******************************************************************************************/

proc sql;
 create table Fitch_ejr as 
 select *
 from ejr as A
 inner join Fitch as B
 on A.ID=B.ID;

run;

data Fitch_EJR;
 set Fitch_EJR;
 FitchMEJR13=Fitch13-ejr13;
 FitchMEJR14=Fitch14-ejr14;
 FitchMEJR15=Fitch15-ejr15;
 FitchMEJR16=Fitch16-ejr16;
 FitchMEJR17=Fitch17-ejr17;
 FitchMEJR18=Fitch18-ejr18;
 FitchMEJR19=Fitch19-ejr19;
 FitchMEJR20=Fitch20-ejr20;
 FitchMEJR21=Fitch21-EJR21;

proc means data=Fitch_ejr n mean median min max;
 var Fitchmejr13 Fitchmejr14 Fitchmejr15 Fitchmejr16 Fitchmejr17 Fitchmejr18 Fitchmejr19 Fitchmejr20 Fitchmejr21;run;
run;
data Fitch_ejr;
 set Fitch_EJR;
 if FitchmEJR13=. then do; Fitch13=.; EJR13=.; end;
 if FitchmEJR14=. then do; Fitch14=.; EJR14=.; end;
 if FitchmEJR15=. then do; Fitch15=.; EJR15=.; end;
 if FitchmEJR16=. then do; Fitch16=.; EJR16=.; end;
 if FitchmEJR17=. then do; Fitch17=.; EJR17=.; end;
 if FitchmEJR18=. then do; Fitch18=.; EJR18=.; end;
 if FitchmEJR19=. then do; Fitch19=.; EJR19=.; end;
 if FitchmEJR20=. then do; Fitch20=.; EJR20=.; end;
 if FitchmEJR21=. then do; Fitch21=.; EJR21=.; end;




* The following sql gets the data on the joint-rating status of Moody's and S&P;
* IF mdy_vs_sp=1 then Moody's has a better rating than S&P;
* IF mdy_vs_sp=-1 then moody's has a worse rating than S&P;
* IF mdy_vs_sp=0 then moody's and S&P have the same rating;
* IF mdy_vs_sp=. then not joint rated by Moody's and S&P;
* the sas data file mdy_sp_joint is created by the sas proram SP_MDY_joint; 

proc sql;
 create table Fitch_EJR_expand as 											
 select *
 from Fitch_EJR as A
 left join libone.mdy_sp_joint as B
 on A.ID=B.CUSIP;

run;

proc means data=Fitch_EJR_expand noprint;
 var Fitch14 Fitch15 Fitch16 Fitch17 Fitch18 Fitch19 Fitch20 Fitch21 EJR14 EJR15 EJR16 EJR17 EJR18 EJR19 EJR20 EJR21;
 output out=joint_mean mean =first2014 first2015 first2016 first2017 first2018 first2019 first2020
first2021 second2014 second2015 second2016 second2017 second2018 second2019 second2020 second2021
 n = obs2014 obs2015 obs2016 obs2017 obs2018 obs2019 obs2020 obs2021; 
 where mdy_vs_sp=0 or mdy_vs_sp=.;									* delete sp and Moody's split rated bonds;
*where mdy_vs_sp=.;
run; 

data joint_mean;
 set joint_mean;
 drop _TYPE_ _FREQ_; 

proc transpose data=joint_mean out=joint_mean_list;

data first;
 set joint_mean_list;
 if index(_Name_, 'first') ne 0;run;

data first;
 set first;
 year=substr(_Name_, 6,4)*1;
 rename col1=first;
 drop _Name_;run;

data second;
  set joint_mean_list;
 if index(_Name_, 'second') ne 0;run;

 data second;
 set second;
 year=substr(_Name_, 7,4)*1;
 rename col1=second;
 drop _Name_;run;

 data third;
  set joint_mean_list;
 if index(_Name_, 'obs') ne 0;run;

 data third;
 set third;
 year=substr(_Name_, 4,4)*1;
 rename col1=Number;
 drop _Name_;run;

data combine;
 merge first second third;
 by year;run;

data combine;
 set combine;
 year=year-1;	

 proc sgplot data=combine;
 series X=year Y=first/ legendlabel='Fitch' markers markerattrs=(color= red symbol =trianglefilled size=10) 
lineattrs=(pattern=8 color=red thickness=3);
 series X=year Y=second / legendlabel='EJR' markers markerattrs=(color= purple symbol =squarefilled size=10) 
lineattrs=(pattern=4 color=purple thickness=3);
yaxis integer min=7 max=10.5 valueattrs=(family='times new roman' size=12) ;
 XAXIS TYPE = DISCRETE valueattrs=(family='times new roman' size=12); run;

proc delete data=first second third combine;run;



/********************************************************************************************/

proc sql;
 create table SP_Fitch as 
 select *
 from SP as A
 inner join Fitch as B
 on A.ID=B.ID;

run;

data SP_Fitch;
 set SP_Fitch;
 SPMFitch13=SP13-Fitch13;
 SPMFitch14=SP14-Fitch14;
 SPMFitch15=SP15-Fitch15;
 SPMFitch16=SP16-Fitch16;
 SPMFitch17=SP17-Fitch17;
 SPMFitch18=SP18-Fitch18;
 SPMFitch19=SP19-Fitch19;
 SPMFitch20=SP20-Fitch20;
 SPMFitch21=SP21-Fitch21;

proc means data=SP_Fitch n mean median min max;
 var SPmFitch13 SPmFitch14 SPmFitch15 SPmFitch16 SPmFitch17 SPmFitch18 SPmFitch19 SPmFitch20 SPmFitch21;run;

data sp_Fitch;
 set sp_Fitch;
 if spmFitch13=. then do; sp13=.; Fitch13=.; end;
 if spmFitch14=. then do; sp14=.; Fitch14=.; end;
 if spmFitch15=. then do; sp15=.; Fitch15=.; end;
 if spmFitch16=. then do; sp16=.; Fitch16=.; end;
 if spmFitch17=. then do; sp17=.; Fitch17=.; end;
 if spmFitch18=. then do; sp18=.; Fitch18=.; end;
 if spmFitch19=. then do; sp19=.; Fitch19=.; end;
 if spmFitch20=. then do; sp20=.; Fitch20=.; end;
 if spmFitch21=. then do; sp21=.; Fitch21=.; end;

* The following sql gets the data on the joint-rating status of Moody's and S&P;
* IF mdy_vs_sp=1 then Moody's has a better rating than S&P;
* IF mdy_vs_sp=-1 then moody's has a worse rating than S&P;
* IF mdy_vs_sp=0 then moody's and S&P have the same rating;
* IF mdy_vs_sp=. then not joint rated by Moody's and S&P;
* the sas data file mdy_sp_joint is created by the sas proram SP_MDY_joint; 

proc sql;
 create table SP_Fitch_expand as 											
 select *
 from SP_fitch as A
 left join libone.mdy_sp_joint as B
 on A.ID=B.CUSIP;

run;


proc means data=sp_Fitch_expand noprint;
 var sp14 sp15 sp16 sp17 sp18 sp19 sp20 sp21 Fitch14 Fitch15 Fitch16 Fitch17 Fitch18 Fitch19 Fitch20 Fitch21;
 output out=joint_mean mean =first2014 first2015 first2016 first2017 first2018 first2019 first2020
first2021 second2014 second2015 second2016 second2017 second2018 second2019 second2020 second2021
 n = obs2014 obs2015 obs2016 obs2017 obs2018 obs2019 obs2020 obs2021; 
where mdy_vs_sp= 0 or mdy_vs_sp=.;											* this where statement exclude all split ratings between Moody's and S&P; 					
* where mdy_vs_sp ne 1;

run; 

data joint_mean;
 set joint_mean;
 drop _TYPE_ _FREQ_; 

proc transpose data=joint_mean out=joint_mean_list;

run;

data first;
 set joint_mean_list;
 if index(_Name_, 'first') ne 0;run;

data first;
 set first;
 year=substr(_Name_, 6,4)*1;
 rename col1=first;
 drop _Name_;run;

data second;
  set joint_mean_list;
 if index(_Name_, 'second') ne 0;run;

 data second;
 set second;
 year=substr(_Name_, 7,4)*1;
 rename col1=second;
 drop _Name_;run;

 data third;
  set joint_mean_list;
 if index(_Name_, 'obs') ne 0;run;

 data third;
 set third;
 year=substr(_Name_, 4,4)*1;
 rename col1=Number;
 drop _Name_;run;

data combine;
 merge first second third;
 by year;run;

 
data combine;
 set combine;
 year=year-1;	

 ods graphics on;
ODS html SGE=ON;

 proc sgplot data=combine;
 series X=year Y=first/ legendlabel='S&P' markers markerattrs=(color= blue symbol =circlefilled size=10) 
lineattrs=(pattern=1 color=blue thickness=3)  ;
 series X=year Y=second / legendlabel='Fitch' markers markerattrs=(color= red symbol =trianglefilled  size=10) 
lineattrs=(pattern=8 color=red thickness=3);
yaxis integer min=7 max=10.5 valueattrs=(family='times new roman' size=12);
 XAXIS TYPE = DISCRETE valueattrs=(family='times new roman' size=12) ; run;

proc delete data=first second third combine;run;

/********************************************************************************************/


proc sql;
 create table mdy_Fitch as 
 select *
 from mdy as A
 inner join Fitch as B
 on A.ID=B.ID;

run;

data mdy_Fitch;
 set mdy_Fitch;
 mdyMFitch13=mdy13-Fitch13;
 mdyMFitch14=mdy14-Fitch14;
 mdyMFitch15=mdy15-Fitch15;
 mdyMFitch16=mdy16-Fitch16;
 mdyMFitch17=mdy17-Fitch17;
 mdyMFitch18=mdy18-Fitch18;
 mdyMFitch19=mdy19-Fitch19;
 mdyMFitch20=mdy20-Fitch20;
 mdyMFitch21=mdy21-Fitch21;

proc means data=mdy_Fitch n mean median min max;
 var mdymFitch13 mdymFitch14 mdymFitch15 mdymFitch16 mdymFitch17 mdymFitch18 mdymFitch19 mdymFitch20 mdymFitch21;run;


data mdy_Fitch;
 set MDY_Fitch;
 if MDYmFitch13=. then do; MDY13=.; Fitch13=.; end;
 if MDYmFitch14=. then do; MDY14=.; Fitch14=.; end;
 if MDYmFitch15=. then do; MDY15=.; Fitch15=.; end;
 if MDYmFitch16=. then do; MDY16=.; Fitch16=.; end;
 if MDYmFitch17=. then do; MDY17=.; Fitch17=.; end;
 if MDYmFitch18=. then do; MDY18=.; Fitch18=.; end;
 if MDYmFitch19=. then do; MDY19=.; Fitch19=.; end;
 if MDYmFitch20=. then do; MDY20=.; Fitch20=.; end;
 if MDYmFitch21=. then do; MDY21=.; Fitch21=.; end;


* The following sql gets the data on the joint-rating status of Moody's and S&P;
* IF mdy_vs_sp=1 then Moody's has a better rating than S&P;
* IF mdy_vs_sp=-1 then moody's has a worse rating than S&P;
* IF mdy_vs_sp=0 then moody's and S&P have the same rating;
* IF mdy_vs_sp=. then not joint rated by Moody's and S&P;
* the sas data file mdy_sp_joint is created by the sas proram SP_MDY_joint; 

proc sql;
 create table MDY_Fitch_expand as 											
 select *
 from MDY_fitch as A
 left join libone.mdy_sp_joint as B
 on A.ID=B.CUSIP;

run;


proc means data=MDY_Fitch_expand noprint;
 var MDY14 MDY15 MDY16 MDY17 MDY18 MDY19 MDY20 MDY21 Fitch14 Fitch15 Fitch16 Fitch17 Fitch18 Fitch19 Fitch20 Fitch21;
 output out=joint_mean mean =first2014 first2015 first2016 first2017 first2018 first2019 first2020
first2021 second2014 second2015 second2016 second2017 second2018 second2019 second2020 second2021
 n = obs2014 obs2015 obs2016 obs2017 obs2018 obs2019 obs2020 obs2021;
where mdy_vs_sp= 0 or mdy_vs_sp=.;											* this where statement exclude all split ratings between Moody's and S&P; 					
* where mdy_vs_sp =-1;
 
run; 

data joint_mean;
 set joint_mean;
 drop _TYPE_ _FREQ_; 

proc transpose data=joint_mean out=joint_mean_list;

data first;
 set joint_mean_list;
 if index(_Name_, 'first') ne 0;run;

data first;
 set first;
 year=substr(_Name_, 6,4)*1;
 rename col1=first;
 drop _Name_;run;

data second;
  set joint_mean_list;
 if index(_Name_, 'second') ne 0;run;

 data second;
 set second;
 year=substr(_Name_, 7,4)*1;
 rename col1=second;
 drop _Name_;run;

 data third;
  set joint_mean_list;
 if index(_Name_, 'obs') ne 0;run;

 data third;
 set third;
 year=substr(_Name_, 4,4)*1;
 rename col1=Number;
 drop _Name_;run;

data combine;
 merge first second third;
 by year;run;

 
data combine;
 set combine;
 year=year-1;

 proc sgplot data=combine;
 series X=year Y=first/ legendlabel='MDY' markers markers markerattrs=(color= orange symbol =diamondfilled size=10) 
lineattrs=(pattern=5 color=orange thickness=3);
 series X=year Y=second / legendlabel='Fitch' markers markerattrs=(color= red symbol =trianglefilled size=10) 
lineattrs=(pattern=8 color=red thickness=3);
yaxis integer min=7 max=10.5 valueattrs=(family='times new roman' size=12);
 XAXIS TYPE = DISCRETE valueattrs=(family='times new roman' size=12); run;

proc delete data=first second third combine;run;
